﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Controls;


namespace Task_Network_Center.General
{
    class SQLBuilder
    {
      

        public string BuildFilter(ComboBox projets, TextBox description, ComboBox inscritPar, DatePicker dateDinscription, TextBox solution, ComboBox executant,
                    TextBox avancement, ComboBox equipe, ComboBox priorite, int echeance, ComboBox departement, int completerLe, ComboBox completerPar,
                TextBox note1, TextBox note2, TextBox note3, TextBox note4, object[,] departementArray, object[,] employeArray, object[,] equipeArray, object[,] prioriteArray)
        {
            string SQLStatement = "";
            DataTable t = new DataTable();

            SQLStatement = "SELECT * FROM tablelistdestaches_general WHERE ";


            if (projets.Text != "*")
            {
                SQLStatement = SQLStatement + "projets = '" + projets.Text + "' AND ";
            }


            if (description.Text != "")
                SQLStatement = SQLStatement + "description LIKE '%" + description.Text + "%' AND ";

            if (inscritPar.Text != "*")
            {
                int inscritReal = 0;

                for (int i = 0; i < employeArray.GetLength(1); i++)
                {
                    if (employeArray[1,i].ToString() == inscritPar.Text)
                    {
                        inscritReal = Convert.ToInt32(employeArray[0, i]);                       
                    }
                }
                SQLStatement = SQLStatement + "`inscrit_par` = " + inscritReal + " AND ";
            }

            string dateInscrit = Convert.ToDateTime(dateDinscription.Text).ToShortDateString();
           
            if (dateDinscription.IsEnabled != false)
                SQLStatement = SQLStatement + "`date_dinscription` = '" + dateInscrit + "' AND ";

            if (solution.Text != "")
                SQLStatement = SQLStatement + "solution LIKE '%" + solution.Text + "%'AND ";


            if (executant.Text != "*")
            {
                int executantReal = 0;

                for (int i = 0; i < employeArray.GetLength(1); i++)
                {
                    if (executant.Text == employeArray[1, i].ToString())
                    {
                        executantReal = Convert.ToInt32(employeArray[0, i]);
                    }
                }
                SQLStatement = SQLStatement + "executant = " + executantReal + " AND ";
            }


            if (avancement.Text != "")
                SQLStatement = SQLStatement + "avancement LIKE '%" + avancement.Text + "%'AND ";

            if (equipe.Text != "*")
            {
                int equipeReal = 0;

                for (int i = 0; i < equipeArray.GetLength(1); i++)
                {
                    if (equipe.Text == equipeArray[1, i].ToString())
                    {
                        equipeReal = Convert.ToInt32(equipeArray[0, i]);
                    }
                }
                SQLStatement = SQLStatement + "equipe = " + equipeReal + " AND ";
            }

            if (priorite.Text != "*")
            {
                int prioriteReal = 0;

                for (int i = 0; i < prioriteArray.GetLength(1); i++)
                {
                    if (priorite.Text == prioriteArray[1, i].ToString())
                    {
                        prioriteReal = Convert.ToInt32(prioriteArray[0, i]);
                    }
                }
                SQLStatement = SQLStatement + "priorite = " + prioriteReal + " AND ";
            }

            if (echeance != 0)
            {
                if (echeance == 1)
                    SQLStatement = SQLStatement + "`echeance` >= '" + DateTime.Today.ToShortDateString() + "' AND ";
                if (echeance == 2)
                    SQLStatement = SQLStatement + "`echeance` < '" + DateTime.Today.ToShortDateString() + "' AND ";
            }


            if (departement.Text != "*")
            {
                int departementReal = 0;

                for (int i = 0; i < departementArray.GetLength(1); i++)
                {
                    if (departement.Text == departementArray[1, i].ToString())
                    {
                        departementReal = Convert.ToInt32(departementArray[0, i]);
                    }
                }
                SQLStatement = SQLStatement + "departement = " + departementReal + " AND ";
            }

            if (completerLe != 0)
            {
                if (completerLe == 1)
                    SQLStatement = SQLStatement + "`complete_le` IS NOT NULL AND ";
                if (completerLe == 2)
                    SQLStatement = SQLStatement + "`complete_le` IS NULL AND ";
            }


            if (completerPar.Text != "*")
            {
                int completeReal = 0;

                for (int i = 0; i < employeArray.GetLength(1); i++)
                {
                    if (completerPar.Text == employeArray[1, i].ToString())
                    {
                        completeReal = Convert.ToInt32(employeArray[0, i]);
                    }
                }
                SQLStatement = SQLStatement + "complete_par = " + completeReal + " AND ";
            }

            if (note1.Text != "")
                SQLStatement = SQLStatement + "note1 LIKE '%" + note1.Text + "%'AND ";

            if (note2.Text != "")
                SQLStatement = SQLStatement + "note2 LIKE '%" + note2.Text + "%'AND ";

            if (note3.Text != "")
                SQLStatement = SQLStatement + "note3 LIKE '%" + note3.Text + "%'AND ";

            if (note4.Text != "")
                SQLStatement = SQLStatement + "note4 LIKE '%" + note4.Text + "%'AND ";

            SQLStatement = SQLStatement.Substring(0, SQLStatement.Length - 4);
            //t = CallStatement.FillTable(SQLStatement);
            return SQLStatement;
        }
        
    }
}
